package BaseDeDatos;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import Clases.Orden;
import Clases.Paso;
import Clases.Usuario;

public class DBOrdenes {

	public DBOrdenes() throws ClassNotFoundException {
		Class.forName("com.mysql.jdbc.Driver");
	}

	public List<Orden> getOrdenes() throws NumberFormatException, SQLException, ParseException, ClassNotFoundException {
		Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/usal", "root", "");
		
		CallableStatement call = (CallableStatement) con.prepareCall("{call select_ordenes()}");
		
		ResultSet ordenesDB = call.executeQuery();
		
		List<Orden> ordenList = this.getEntities(ordenesDB);
		
		con.close();
		
		return ordenList;		
	}
	
	public List<Orden> getOrdenes(Usuario u) throws SQLException, NumberFormatException, ClassNotFoundException {
		Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/usal", "root", "");
		
		CallableStatement call = (CallableStatement) con.prepareCall("{call select_ordenes_operario(?)}");
		call.setString(1, u.getLegajo());
		
		
		ResultSet ordenesDB = call.executeQuery();
		
		List<Orden> ordenList = this.getEntities(ordenesDB);
		
		con.close();
		
		return ordenList;		
	}
	
	public void guardarOrden(Orden o) throws SQLException {
		Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/usal", "root", "");
		
		CallableStatement call = (CallableStatement) con.prepareCall("{call select_last_orden_id(?)}");
		call.registerOutParameter(1, java.sql.Types.VARCHAR);
		
		call.executeQuery();
		
		String id = call.getString(1);
		
		String number = null;
		Integer next;
		
		String year = ((Integer)Calendar.getInstance().get(Calendar.YEAR)).toString();
		year = year.substring(year.length() - 2, year.length());
		
		if (id != null) {
			number = id.split("/")[0];
			next = Integer.parseInt(number) + 1;
			
			number = "00000" + next.toString();
			number = number.substring(number.length() - 4, number.length());
			
			id = number + "/" + year;
		} else {
			id = "0001" + "/" + year;
		}
		
		o.setID(id);
		
		call = (CallableStatement) con.prepareCall("{call insert_orden(?,?,?,?,?,?,?)}");
		call.setString(1, o.getID());
		call.setString(2, o.getFechaAlta());
		call.setInt(3, o.getProducto().getID());
		call.setInt(4, o.getCantidad());
		call.setString(5, o.getEstado());
		call.setString(6, o.getFechaEstimada());
		call.setString(7, o.getDescripcion());
		
		call.executeQuery();
		
		call = (CallableStatement) con.prepareCall("{call insert_orden_pasos(?,?,?,?,?,?,?)}");
		
		for (Paso p : o.getPasos()) {
			call.setString(1, o.getID());
			call.setString(2, p.getUsuario().getLegajo());
			call.setString(3, p.getEstado());
			call.setString(4, p.getDescripcion());
			
			if (p.necesitaMateriaPrima()) {
				call.setInt(5, p.getMateriaPrima().getId());
				call.setInt(6, p.getMateriaPrima().getCantidad());
			} else {
				call.setNull(5, Types.NULL);
				call.setNull(6, Types.NULL);
			}
			
			if (p.getFechaFinalizacion() != null) {
				call.setString(7, p.getFechaFinalizacion());
			} else {
				call.setNull(7, Types.NULL);
			}
			
			call.executeQuery();
		}
		
		con.close();
	}

	public Orden getOrden(String id) {
		List<Orden> ordenList = null;
		
		try {
			ordenList = this.getOrdenes();
		} catch (NumberFormatException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ParseException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		Orden o = null;
		for (int i = 0; i < ordenList.size(); i++) {
			if(ordenList.get(i).getID().equals(id)) {
				o = ordenList.get(i);
				break;
			}
		}
		
		try {
			o.setPasos(new DBProductos().getPasos(o));
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		return o;
	}
	
	private List<Orden> getEntities(ResultSet rs) throws NumberFormatException, SQLException, ClassNotFoundException {
		List<Orden> ordenList = new ArrayList<Orden>();

		while (rs.next()) {
			Orden o = new Orden();
			
			o.setID(rs.getString(1));
			
			o.setFechaAlta(rs.getString(2));
			
			o.setProducto(new DBProductos().getProducto(Integer.parseInt(rs.getString(3))));
			
			o.setCantidad(Integer.parseInt(rs.getString(4)));
			
			o.setEstado(rs.getString(5));
			
			o.setFechaEstimada(rs.getString(6));
			
			o.setDescripcion(rs.getString(7));
			
			ordenList.add(o);
		}
		return ordenList;
	}

	public void actualizarOrden(Orden o) throws SQLException {
		Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/usal", "root", "");
		
		CallableStatement call = (CallableStatement) con.prepareCall("{call update_orden(?,?)}");	
		call.setString(1, o.getID());
		call.setString(2, o.getEstado());
		
		call.executeQuery();

		for (int i = 0; i < o.getPasos().size(); i++) {
			call = (CallableStatement) con.prepareCall("{call update_orden_pasos(?,?,?)}");
			
			int pasoID = o.getPasos().get(i).getId();
			String estado = o.getPasos().get(i).getEstado();
			
			call.setInt(1, pasoID);
			call.setString(2, estado);
			
			if (o.getPasos().get(i).getFechaFinalizacion() != null) {
				call.setString(3, o.getPasos().get(i).getFechaFinalizacion());
			} else {
				call.setNull(3, Types.NULL);
			}
			
			call.executeQuery();
			
			if (o.getPasos().get(i).necesitaMateriaPrima() && o.getPasos().get(i).getEstado().equals(Paso.ESTADO_INICIADO)) {
				call = (CallableStatement) con.prepareCall("{call select_materia_prima_stock(?,?)}");
				call.setInt(1, o.getPasos().get(i).getMateriaPrima().getId());
				call.registerOutParameter(2, Types.INTEGER);
				
				call.executeQuery();
				
				Integer cantidad = call.getInt(2);
				cantidad -= o.getPasos().get(i).getMateriaPrima().getCantidad();
				
				call = (CallableStatement) con.prepareCall("{call update_materia_prima_stock(?,?)}");
				call.setInt(1, o.getPasos().get(i).getMateriaPrima().getId());
				call.setInt(2, cantidad);
				
				call.executeQuery();
			}
		}
		con.close();
	}
}
